﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CAR_REPORT_Car2010r_Get_Monthly_Avg_Fuel_Efficiency')
	BEGIN
		DROP Procedure usp_UPDMS_CAR_REPORT_Car2010r_Get_Monthly_Avg_Fuel_Efficiency
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CAR_REPORT_Car2010r_Get_Monthly_Avg_Fuel_Efficiency
**	Desc : 주유일별/월평균 주유정보
**	Test Exec Query : Exec usp_UPDMS_CAR_REPORT_Car2010r_Get_Monthly_Avg_Fuel_Efficiency 'CAR01', '2010-04-01','2010-10-22'
**	Called by : Car_Dac_UPDMS_CAR_REPORT_Car2010r.cs
**	Program ID : Car2010r
**	Auth : 송시명
**	Date : 2010-10-22
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CAR_REPORT_Car2010r_Get_Monthly_Avg_Fuel_Efficiency]
@ls_car_id nvarchar(5),
@ls_refuelling_dt_from nvarchar(10),
@ls_refuelling_dt_to nvarchar(10)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @sql nvarchar(max)

SET @sql = '
WITH rslt AS (
     SELECT Refuelling_Dt,
            MAX(Distance) AS Distance,
            SUM(Quantity) AS Quantity,
            AVG(Amount_Per_Liter) AS Amount_Per_Liter, 
            SUM(Amount) AS Amount
       FROM UPDMS_CAR_REFUELLING WITH(NOLOCK)
      WHERE Car_Id = @ls_car_id
        AND Refuelling_Dt >= @ls_refuelling_dt_from
        AND Refuelling_Dt <= @ls_refuelling_dt_to
      GROUP BY Refuelling_Dt
)
SELECT Refuelling_Dt,
       Fuel_Efficiency AS Monthly_Fuel_Efficiency
  FROM
     (
       SELECT LEFT(Refuelling_Dt, 7) AS Refuelling_Dt,
              AVG(Fuel_Efficiency) AS Fuel_Efficiency
         FROM
            (
              SELECT Refuelling_Dt,
                     Distance,
                     Quantity,
                     Amount_Per_Liter,
                     Amount,
                     Fuel_Efficiency = ISNULL(CONVERT(DECIMAL(18,2), ABS( (Distance - 
                              (SELECT Distance 
                                 FROM rslt
                                WHERE Refuelling_Dt = (SELECT min(Refuelling_Dt) 
                                                         FROM rslt 
                                                        WHERE Refuelling_Dt > a.Refuelling_Dt))) / (Quantity * 1.0)
                                                           ) --abs
                               ) --convert
                         ,0.00) --isnull
                FROM rslt AS a 
            ) x
            GROUP BY LEFT(Refuelling_Dt, 7)
     ) y
UNION ALL
SELECT ''Total Average'' AS Refuelling_Dt,
       SUM(Fuel_Efficiency) / (MAX(Num) - 1) AS Total_Fuel_Efficiency
  FROM
     (
       SELECT ROW_NUMBER() OVER( ORDER BY x.Refuelling_Dt) AS Num,
              Fuel_Efficiency
         FROM
            (
              SELECT Refuelling_Dt,
                     Distance,
                     Quantity,
                     Amount_Per_Liter,
                     Amount,
                     Fuel_Efficiency = ISNULL(CONVERT(DECIMAL(18,2), ABS( (Distance - 
                              (SELECT Distance 
                                 FROM rslt
                                WHERE Refuelling_Dt = (SELECT min(Refuelling_Dt) 
                                                         FROM rslt 
                                                        WHERE Refuelling_Dt > a.Refuelling_Dt))) / (Quantity * 1.0)
                                                           ) --abs
                               ) --convert
                         ,0.00) --isnull
                FROM rslt AS a 
            ) x
     ) y
'

DECLARE @param nvarchar(max)
SET @param = '
  @ls_car_id nvarchar(5),
  @ls_refuelling_dt_from nvarchar(10),
  @ls_refuelling_dt_to nvarchar(10)
'

--print(@sql)
EXEC sp_executesql @sql, @param,
                   @ls_car_id = @ls_car_id,
                   @ls_refuelling_dt_from = @ls_refuelling_dt_from,
                   @ls_refuelling_dt_to = @ls_refuelling_dt_to

GO 